package cz.acies.uniql.dialect;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Enumeration;
import java.util.Hashtable;
import java.util.Iterator;
import java.util.List;
import java.util.Properties;
import java.util.StringTokenizer;


import cz.acies.uniql.connect.SQLConnect;
import cz.acies.uniql.metadata.FieldMetaData;
import cz.acies.uniql.metadata.IndexesMetaData;
import cz.acies.uniql.sql.AlterTable;
import cz.acies.uniql.sql.SelectQuery;
import cz.acies.uniql.sql.UniQLExpr;
import cz.acies.uniql.sql.element.UniQLField;
import cz.acies.uniql.sql.element.UniQLLimit;
import cz.acies.uniql.sql.functions.SQLFunc;

// TODO: Auto-generated Javadoc
/**
 * The Class PostgreDialect.
 */
public class PostgreDialect extends Dialect
{
	private static final long serialVersionUID = 8082389864894655223L;
	
	// DBMetaData variables
	// TableMetaData variables
	// FieldMetadata variables
	// PrimaryKeyMetaData variables
	// IndexMetaData variables
	
	// Convert Element variables
	// Convert values variables

	/**
	 * Instantiates a new postgre dialect.
	 */
	public PostgreDialect() {
		super();

		registerColumnType( Types.BIT, "bit" );
		registerColumnType( Types.BIGINT, "numeric" );
		registerColumnType( Types.SMALLINT, "smallint" );
		registerColumnType( Types.TINYINT, "integer" );
		registerColumnType( Types.INTEGER, "integer" );
		registerColumnType( Types.CHAR, "varchar($l)" );
		registerColumnType( Types.VARCHAR, "varchar($l)" );
		registerColumnType( Types.FLOAT, "numeric($p,$s)" );
		registerColumnType( Types.DOUBLE, "numeric($p,$s)" );
		registerColumnType( Types.REAL, "numeric($p,$s)" );
		registerColumnType( Types.DATE, "date" );
		registerColumnType( Types.TIME, "time" );
		registerColumnType( Types.TIMESTAMP, "timestamp" );
		registerColumnType( Types.VARBINARY, "varbinary($l)" );
		registerColumnType( Types.NUMERIC, "numeric($p,$s)" );
		registerColumnType( Types.LONGVARCHAR, "text" );

		// ----- register functions -------------------------------------------
		
		// Special functions 
		//registerFunction("rownumber", "");
		//registerFunction("connection_id", "(SELECT SYS_CONTEXT ('USERENV', 'SESSION_USERID') FROM dual)");
		registerFunction("connection_id", "'0'");
		registerFunction("over", "");
		
		// Convert functions
		registerFunction("convert", "$1");
		registerFunction("text_to_varchar", "$1"); 					//CONVERT($1, VARCHAR(2048));
		registerFunction("to_varchar", "TO_CHAR($1, $2)");
		registerFunction("to_number", "TO_NUMBER($1, '99999999999')");
		registerFunction("to_date", "TO_DATE($1, 'YYYY-MM-DD')");
		registerFunction("to_timestamp", "TO_TIMESTAMP($1, 'YYYY-MM-DD HH24:MI:SS')");
		registerFunction("date_to_varchar", "TO_CHAR($1)");
		//registerFunction("number_to_varchar", "TO_CHAR($1, )");
		
		// String functions
		registerFunction("left", "SUBSTR($1, 1, $2)");
		registerFunction("right", "SUBSTR($1, LENGTH($1)-$2, $2)");
		registerFunction("substring", "SUBSTR($1, $2, $3)");
		registerFunction("ltrim", "LTRIM($1)");
		registerFunction("rtrim", "RTRIM($1)");
		registerFunction("trim", "LTRIM(RTRIM($1))");
		registerFunction("lpad", "LPAD($1, $2, $3)");
		registerFunction("rpad", "RPAD($1, $2, $3)");
		registerFunction("length", "LENGTH($1)");
		registerFunction("char_length", "CHAR_LENGTH($1)");
		registerFunction("char", "CHR($1)");
		
		registerFunction("concat", "($n{ || })");
		//registerFunction("password", "$1");
		registerFunction("locate", "STRPOS($2, $1, $3)");
		registerFunction("replace", "REPLACE($1, $2, $3)");
		registerFunction("chrtran", "TRANSLATE($1, $2, $3)");
		
		// Date & time functions
		registerFunction("now", "NOW()");
		registerFunction("dateadd", "$3 + INTERVAL '$2 $1'");
		registerFunction("date_add", "($3, INTERVAL '$2 $1')");
		//registerFunction("date_format", "DATE_FORMAT($1, $2)");
		registerFunction("to_days", "");
		registerFunction("datediff_day", "($1 - $2)");        // Vrac� po�et dn�
		registerFunction("year", "DATE_PART('year', $1)");
		registerFunction("month", "DATE_PART('month', ů1)");
		registerFunction("day", "DATE_PART('day', $1)");

		// Logic functions
		registerFunction("if", "(CASE WHEN $1 THEN $2 ELSE $3 END)");
		//registerFunction("isnull", "$1 IS NULL");
		registerFunction("not isnull", "$1 NOTNULL");
//		registerFunction("ifnull", "NULLIF($1, $2)");
		registerFunction("ifnull", "(CASE WHEN $1 IS NULL THEN $2 ELSE $1 END)");
		registerFunction("if_isnull", "(CASE WHEN $1 IS NULL THEN $2 ELSE $3 END)");

		// Numericfunctions
		registerFunction("abs", "ABS($1)");
		registerFunction("round", "ROUND($1, $2)");

		// -----  -------------------------------------------------------------
		
		registerKeyword("ROW", "ROW_");
		registerKeyword("audit", "audit");
	
		// DBMetaData variables
		// TableMetaData variables
		// FieldMetadata variables
		FMD_IDENTITY_BUILD_PART = "NEXTVAL('identity_sequence')";
		FMD_IDENTITY_PARSE_COLUNM_NAME = "REMARKS";
		//FMD_NULL_COLUMN_STRING = "NULL";
		
		// PrimaryKeyMetaData variables
		PK_CONSTRAINTED = true;
		
		// IndexMetaData variables
		// Convert Element variables
		SQL_UPDATE_JOIN_WITH_FROM = true;
		SQL_DELETE_JOIN_WITH_FROM = true;
		
		// Convert values variables
	}
	
	// ----- Quoted methods ---------------------------------------------------

	/* (non-Javadoc)
	 * @see cz.acies.uniql.dialect.Dialect#LQ()
	 */
	public String LQ() { return "\""; }
	
	/* (non-Javadoc)
	 * @see cz.acies.uniql.dialect.Dialect#RQ()
	 */
	public String RQ() { return "\""; }
	
	/* (non-Javadoc)
	 * @see cz.acies.uniql.dialect.Dialect#CTI_LQ()
	 */
	public String CTI_LQ() { return ""; }
	
	/* (non-Javadoc)
	 * @see cz.acies.uniql.dialect.Dialect#CTI_RQ()
	 */
	public String CTI_RQ() { return ""; }
	
	// ----- Database connection methods --------------------------------------
	
	/* (non-Javadoc)
	 * @see cz.acies.uniql.dialect.Dialect#getSQLConnPrefix()
	 */
	public String getSQLConnPrefix() {
		return "jdbc:postgresql:";
	}
	
	/* (non-Javadoc)
	 * @see cz.acies.uniql.dialect.Dialect#getSQLConnDriver()
	 */
	public String getSQLConnDriver() {
		return "org.postgresql.Driver";
	}
	
	/*
	public String genDBUrl(Properties prop) throws SQLException {
		String url = this.generateSQLConnUrl(prop);
		return url;	
	}
	*/
	
	/* (non-Javadoc)
	 * @see cz.acies.uniql.dialect.Dialect#generateSQLConnUrl(cz.acies.uniql.connect.SQLConnect)
	 */
	public String generateSQLConnUrl(SQLConnect sqlconn) throws SQLException {
		
		//"jdbc:postgresql://localhost:5432/database"
		
		String prefix = sqlconn.getPrefix();
		String host = sqlconn.getHost();
		String port = sqlconn.getPort();//(String)prop.get("port");
		String database = sqlconn.getDatabase();
		
		sqlconn.getProperties().remove("host");
		sqlconn.getProperties().remove("port");
		sqlconn.getProperties().remove("database");
		
		//String schema = (String)sqlconn.getProperty("schema");
		/*
		if (schema != null) {
			sqlconn.setSQLConnSchema(schema);
			this.setSchema(schema);
		}
		*/
		
		String url = prefix+"//"+host+":"+port+"/"+database;
		return url;
	}
	
	/* (non-Javadoc)
	 * @see cz.acies.uniql.dialect.Dialect#parseURL(java.lang.String)
	 */
	public Properties parseURL(String url) {
		try {
			Properties prop = new Properties();
			
			String shl = "";
			String shr = "";
			
			int pos = url.indexOf("?");
			if (pos > -1) {
				shl = url.substring(0, pos);
				shr = url.substring(pos+1);
			} else {
				shl = url;
			}
			if (shl != null && shl.length() > 0) {
				pos = shl.indexOf("//");
				if (pos > -1) {
					String prefix = shl.substring(0, pos);
					shl = shl.substring(pos+2);
					prop.put("prefix", prefix);
				} 
				pos = shl.indexOf("/");
				if (pos > -1) {
					String host = shl.substring(0, pos);
					shl = shl.substring(pos+1);
					pos = host.indexOf(":");
					if (pos > -1) {
						String port = host.substring(pos+1);
						host = host.substring(0, pos);
						prop.put("port", port);
					}
					prop.put("host", host);
				}
				if (shl.length() > 0) {
					String database = shl;
					prop.put("database", database);
				}
			}
			if (shr != null && shr.length() > 0) {
				StringTokenizer st = new StringTokenizer(shr,"&");
				while (st.hasMoreTokens()) {
					String element = st.nextToken();
					pos = element.indexOf("=");
					if (pos > -1) {
						String key = element.substring(0, pos);
						String value = "";
						if (pos < element.length()) value = element.substring(pos+1, element.length());
						prop.put(key, value);
					}
				}
			}
			//System.out.println("prop = "+prop);			
			return prop;
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}

	/* (non-Javadoc)
	 * @see cz.acies.uniql.dialect.Dialect#getSchema()
	 */
	public String getSchema() {
		return "public";
	}
	
	/* (non-Javadoc)
	 * @see cz.acies.uniql.dialect.Dialect#createStatement(java.sql.Connection)
	 */
	public synchronized Statement createStatement(Connection conn) throws SQLException {
		return conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); 	
	}
	
	/* (non-Javadoc)
	 * @see cz.acies.uniql.dialect.Dialect#createStatement(java.sql.Connection, int)
	 */
	public synchronized Statement createStatement(Connection conn, int style) throws SQLException {
		int concur = ResultSet.CONCUR_READ_ONLY;
		switch (style) {
			case 0: concur = ResultSet.CONCUR_READ_ONLY; break;
			case 1: concur = ResultSet.CONCUR_UPDATABLE; break;
			default:
				break;
		}
		return conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, concur); 	
	}
		
	// ----- ColumnType methods -----------------------------------------------
	
	// ----- ReservedKeywords methods -----------------------------------------
	
	// ----- Fn Methods -------------------------------------------------------
	
	//* ***********************************************************************
	//* Database Mapping methods
	//* ***********************************************************************
	
	// ----- DBMetaData methods -----------------------------------------------
	
	// ----- TableMetaData metdods --------------------------------------------
	
	// ----- FieldMetadata methods --------------------------------------------
	
	//public String getNullColumnString() { return "NULL"; }

	// ----- PrimaryKey Constraint --------------------------------------------

	/* (non-Javadoc)
	 * @see cz.acies.uniql.dialect.Dialect#getPKConstraintKey(java.lang.String, java.lang.String)
	 */
	public String getPKConstraintKey(String table, String column) {
		String key = table+"_pkey"; //+"_"+column;
		return key;
	}
	/*	
	public boolean isPKConstrainted() { 
		return true; 
	}
	*/
	// ----- IndexMetaData methods --------------------------------------------

	/**
	 * Funkce vygeneruje n�zev pro INDEX k tabilce.
	 * 
	 * @param tableName the table name
	 * @param indexName the index name
	 * @param sufix the sufix
	 * @return the string
	 */		
	public String generateIndexName(String tableName, String indexName, String sufix) {
		String name = tableName.toLowerCase()+"_idx"+"_"+indexName.toLowerCase();
		return name;
	}
	
	// ----- Convert Element methods ------------------------------------------
	
	/* (non-Javadoc)
	 * @see cz.acies.uniql.dialect.Dialect#getFromAsString()
	 */
	public String getFromAsString() {
		return " AS ";
	}
	
	/* (non-Javadoc)
	 * @see cz.acies.uniql.dialect.Dialect#transferAlias(java.lang.String)
	 */
	public String transferAlias(String alias) {
		return alias.toLowerCase();
	}

	/* (non-Javadoc)
	 * @see cz.acies.uniql.dialect.Dialect#tableNameToCase(java.lang.String)
	 */
	public String tableNameToCase(String tableName) {
		//return tableName.toUpperCase();
		return tableName;
	}
	
	/* (non-Javadoc)
	 * @see cz.acies.uniql.dialect.Dialect#columnNameToCase(java.lang.String)
	 */
	public String columnNameToCase(String columnName) {
		//return tableName.toUpperCase();
		return columnName;
	}

	// ----- Convert Values methods -------------------------------------------
	
	// ----- DDL methods ------------------------------------------------------

	// ----- FullText methods -------------------------------------------------

	// ----- LOCK & UNLOCK TABLE methtods -------------------------------------

	// ----- QueryRebuilder methods -------------------------------------------
	
	/**
	 * CREATE USER SMART4WEB_CCONNECT
	 * IDENTIFIED BY "smart"
	 * ACCOUNT UNLOCK;.
	 * 
	 * @return the creates the database string
	 */
	public String getCreateDatabaseString() {
		return null;
	}
	
	/**
	 * CREATE TABLE SMART4WEB_ELCO."acc_day_book" (<br>
	 * IDU VARCHAR2(10),<br>
	 * HEADER VARCHAR2(200) DEFAULT '' NOT NULL,<br>
	 * PRIMARY KEY (<br>
	 * IDU<br>
	 * ) <br>
	 * )<br>
	 * 
	 * @param fmd the fmd
	 * @return the creates the table field string
	 * @see cz.acies.uniql.dialect.Dialect#getCreateTableString(com.smart4web.db.structure.TableMetaData)
	 */
	/*
	public String getCreateTableString(TableMetaData tbmd) {
		String schema = (this.getSchema() != null) ? this.getSchema() : "" ;
		PrimaryKeyMetaData pkmd = tbmd.getPrimaryKey();
		
		String s = "CREATE TABLE \""+schema+"\".\""+tbmd.getName()+"\" (\n";
		for (int i = 0; i < tbmd.getFields().size(); i++) {
			FieldMetaData fmd = (FieldMetaData)tbmd.getFields().elementAt(i);
			s += fmd.toString();			
			if (i < tbmd.getFields().size()-1) {
				s += ",\n";
			} else {
				if (pkmd != null) s += ",\n"; 
				else s += "\n";
			}
		}		
		if (pkmd != null) {
			String columnname = pkmd.getColumn();
			s += "PRIMARY KEY ( \""+columnname+"\" ) ";
		}
		s += ")";
		
		return s;
	}
	*/
	
	/* (non-Javadoc)
	 * @see cz.acies.uniql.dialect.Dialect#getCreateTableFieldString(com.smart4web.db.structure.FieldMetaData)
	 */
	public String getCreateTableFieldString(FieldMetaData fmd) {
		String s = "";
		if (fmd.isPrefix()) s += this.LQ() + fmd.getTable() + this.RQ() + ".";
		s += (isKeyword(fmd.getName())) ? 
		    this.LQ()+(fmd.getName()+"_")+this.RQ() : 
		    this.LQ()+fmd.getName()+this.RQ();
		    
		fmd.setTypeName(this.getColumnTypeString(
		    fmd.getSqlType(), fmd.getSize(), fmd.getDecimal())
		);
		s += " " + fmd.getTypeName() + " ";
	
		if (fmd.isNullable()) {
			if (fmd.hasValue()) {
				s += "DEFAULT " + fmd.getValue() + " ";
			}
			s += this.getNullColumnString();
		} else {
			s += "DEFAULT " + fmd.getValue() + " ";
			s += " NOT NULL ";
		}
		return s;
	}

	/* (non-Javadoc)
	 * @see cz.acies.uniql.dialect.Dialect#getDropTableString(java.lang.String, java.lang.String)
	 */		
	public String getDropTableString(String schema,String table) {
		schema = this.getSchema();
		return "DROP TABLE \""+schema+"\".\""+table+"\"";
	}

	/* (non-Javadoc)
	 * @see cz.acies.uniql.dialect.Dialect#getCreateIndexesString(com.smart4web.db.structure.IndexesMetaData)
	 */	
	public String getCreateIndexesString(IndexesMetaData ixmd) {
		List<String> list = getCreateIndexesList(ixmd);
		String s = "";
		for (Iterator<String> iterator = list.iterator(); iterator.hasNext();) {
			String query = iterator.next();
			s += query + ((iterator.hasNext()) ? "; " : "");
		}	 		
		return s;
	}

	/* (non-Javadoc)
	 * @see cz.acies.uniql.dialect.Dialect#getCreateIndexesList(com.smart4web.db.structure.IndexesMetaData)
	 */
	public List<String> getCreateIndexesList(IndexesMetaData ixmd) {
		List<String> list = new ArrayList<String>();
		//String schema = (ixmd.getSchema() != null) ? ixmd.getSchema() : "" ;
		String schema = this.getSchema();
		/*
		CREATE INDEX "a_test_idx" ON "public"."a_test" USING btree ("HEADER");
		CREATE UNIQUE INDEX "language_idx_login" ON "public"."language" USING btree ("IDU", "LANGUAGE");
		*/
		for (Enumeration e = ixmd.elements(); e.hasMoreElements();) {
			Hashtable params = (Hashtable)e.nextElement();
			Hashtable columns = (Hashtable)params.get("columns");
			
			String name = (String)params.get("INDEX_NAME");
			String tableName = ixmd.getTable();
			if (isKeyword(tableName.toUpperCase())) {
				tableName = getKeywords().getKeywordAlter(tableName.toUpperCase());
				ixmd.setTable(tableName);
			}
			//System.out.println("INDEX_NAME = "+name);	
			String query = "";
			if (name.equals("PRIMARY")) {
			} else {
				//String asc_desc = (String)params.get("ASC_OR_DESC");
				boolean nonunique = ((Boolean)params.get("NON_UNIQUE")).booleanValue();
				
				String colstr = "( ";
				for (int i = 0; i < columns.size(); i++) {
					String columnname = (String)columns.get(new Integer(i+1));
					//columnname = (ReservedKeywords.isReservedKeyword(columnname)) ? (columnname+"_") : columnname;
					colstr += LQ() + columnname + RQ(); 					
					if (i < columns.size()-1) colstr += ",";
				}
				colstr += " )";
								
				query = "CREATE ";
				if (!nonunique) { 
					query += "UNIQUE ";
				}
				// query += asc_desc;
				//query += "INDEX "+name+" ON "+schema+".\""+ixmd.getTable().toUpperCase()+"\" "+colstr;
				query += "INDEX "+name+" ON "+schema+".\""+ixmd.getTable().toLowerCase()+"\" USING btree "+colstr;
				list.add(query);
			}
		}
		return list;
	}
	
	/* (non-Javadoc)
	 * @see cz.acies.uniql.dialect.Dialect#getDropIndexString(java.lang.String, java.lang.String)
	 */
	public String getDropIndexString(String tableName, String indexName) {
		return "DROP INDEX "+tableName+" ON ["+indexName+"]";
	}
	
	/* (non-Javadoc)
	 * @see cz.acies.uniql.dialect.Dialect#execAlterTable(cz.acies.uniql.sql.AlterTable, java.sql.Connection)
	 */
	public synchronized void execAlterTable(AlterTable alterTable, Connection conn) throws Exception {
	}

	// ----- LOCK & UNLOCK TABLE methods --------------------------------------
	
	/* (non-Javadoc)
	 * @see cz.acies.uniql.dialect.Dialect#lockTable(java.lang.String)
	 */
	public String lockTable(String table) {
		String sql = "BEGIN WORK; LOCK TABLE "+table+" IN ROW EXCLUSIVE MODE;";
		return sql;	 
	}

	/* (non-Javadoc)
	 * @see cz.acies.uniql.dialect.Dialect#unlockTable(java.lang.String)
	 */
	public String unlockTable(String table) {
		String sql = "COMMIT WORK;"; 
		return sql;
	}

	// ----- Limit Query method -----------------------------------------------
	
	/* (non-Javadoc)
	 * @see cz.acies.uniql.dialect.Dialect#generateLimitQuery(cz.acies.uniql.sql.SelectQuery)
	 */
	public synchronized String generateLimitQuery(SelectQuery query) throws Exception {
		UniQLLimit limit = query.getLimit();
		int first = limit.getFirstResult();
		int count = limit.getCountResult();
		
		String s = query.translate()+"\n";
		s += "LIMIT "+count+" OFFSET "+first;
		return s;
	}

	// ----- RowCount methods -------------------------------------------------
	
	/* (non-Javadoc)
	 * @see cz.acies.uniql.dialect.Dialect#generateRowCountQuery(cz.acies.uniql.sql.SelectQuery)
	 */
	public synchronized SelectQuery generateRowCountQuery(SelectQuery query) throws Exception {
		try {
			SelectQuery sql = (SelectQuery)query.clone();
			//HashMap<String, Object> aliasMap = query.getAliasMap();
			
			sql.removeOrderBy();
			ArrayList<UniQLExpr> fields = sql.getFields();
			fields.clear();
			sql.removeLimit();
			sql.removeOrderBy();
			sql.removeGroupBy();
			UniQLField f = new UniQLField(new SQLFunc("COUNT").addParam("*")).as("ROW_COUNT");
			sql.addField(f);
			return sql;
		} catch (Exception e) {
			e.printStackTrace();
		}
		return query;
	}

	// ----- GroubBy method ---------------------------------------------------
	
	/* (non-Javadoc)
	 * @see cz.acies.uniql.dialect.Dialect#generateGroupByQuery(cz.acies.uniql.sql.SelectQuery)
	 */	
	public synchronized SelectQuery generateGroupByQuery(SelectQuery query) {
		query.setDistinct(true);
		query.removeGroupBy();
		return query;
	}
	
	/* (non-Javadoc)
	 * @see cz.acies.uniql.dialect.Dialect#isGroupByRewrite()
	 */
	public boolean isGroupByRewrite() {
		return true;
	}	
}
